#coding=utf-8
__author__ = "***"
__date__ = "2018/5/30　16:50"

# 读写excel工作表
import  xdrlib ,sys
import xlrd
import os
from pathlib import Path
from urllib.request import urlretrieve
import socket
import requests

socket.setdefaulttimeout(3)


filename='./4.xls'
def open_excel(file=filename):
    try:
        data = xlrd.open_workbook(file)
        return data
    except Exception as e:
        print("文件打开失败,str(e)是",str(e))

#根据索引获取Excel表格中的数据   参数:file：Excel文件路径     colnameindex：表头列名所在行的所以  ，by_index：表的索引
def excel_table_byindex(file=filename,colnameindex=0,by_index=0):
    data = open_excel(file)
    table = data.sheets()[by_index]
    nrows = table.nrows #行数
    ncols = table.ncols #列数
    colnames =  table.row_values(colnameindex) #某一行数据
    list =[]
    for rownum in range(1,nrows):
         row = table.row_values(rownum)
         if row:
             app = {}
             for i in range(len(colnames)):
                app[colnames[i]] = row[i]
             list.append(app)
    return list

# 执行下载
def download(file_path, picture_url):
	headers = {
		"User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 			(KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36 QIHU 360SE",
		}
	r = requests.get(picture_url, headers=headers)
	with open(file_path, 'wb') as f:
		f.write(r.content)

def main():
   tables = excel_table_byindex()
   # 获取相对地址
   path = os.path.abspath('.') 
   print(path)
   for row in tables:
       print(row['订单编号'])
    #    print(row['定制图片'])
       nums = row['订单编号']
       pic_address = row['定制图片']
       lists = pic_address.split(";")
       i = 0
       for list in lists:
            print(list)
            i = i + 1
            filename = os.path.basename(list)
            filename = str(i) + "." +filename.split(".")[1]
            print(filename)
            dstDir = f"path/{nums}"
            path_obj = Path(dstDir)
            if not path_obj.exists():   os.makedirs(dstDir)
            try:
                urlretrieve(list, f"{dstDir}/{filename}")
            except:
                print(f"重试一次: {list}")
                try:
                    urlretrieve(list, f"{dstDir}/{filename}")
                except:
                    print(f"Download file timeout: {list}")
                    with open("./download_failed_list.txt", "a") as f:
                        f.write(list + "\n")
                
            # download(path + "/"+nums+"/", list)

''' 
    {
        "订单编号": "4603107574001", 
        "SKU": "YJT01-film", 
        "商品中文名称": "定制记忆胶卷-黄-10张照片+精品包装", 
        "定制图片": "https://image-uploader-usf2.sfo2.cdn.digitaloceanspaces.com/2021/Dec/10/thiccslides/b3463528-5ef0-4fe8-9081-076e21c6b644.jpg;https://image-uploader-usf.sfo2.cdn.digitaloceanspaces.com/2021/Dec/10/thiccslides/62f0d6a5-bcec-4c65-8a9e-508df6dddeea.jpg;https://image-uploader-uny.nyc3.cdn.digitaloceanspaces.com/2021/Dec/10/thiccslides/d9cd83ec-fdf5-4c1b-a070-f6470cfed957.jpg;https://image-uploader-uny.nyc3.cdn.digitaloceanspaces.com/2021/Dec/10/thiccslides/30bfeed7-98e9-46a2-b6b7-69b7e4af957e.jpg;https://image-uploader-uny.nyc3.cdn.digitaloceanspaces.com/2021/Dec/10/thiccslides/accc8004-fade-4033-ac64-06223d28816b.jpg;https://image-uploader-usf.sfo2.cdn.digitaloceanspaces.com/2021/Dec/10/thiccslides/92f57d64-1eb5-494f-b44f-88b6694cf83e.jpg;https://image-uploader-uny.nyc3.cdn.digitaloceanspaces.com/2021/Dec/10/thiccslides/1b001397-993e-4fb5-b154-eee402370539.jpg;https://image-uploader-uny.nyc3.cdn.digitaloceanspaces.com/2021/Dec/10/thiccslides/69dc9988-d7fe-4966-ad8c-f2cbd460dda2.jpg;https://image-uploader-uny.nyc3.cdn.digitaloceanspaces.com/2021/Dec/10/thiccslides/de7d0995-a7c3-4088-8e71-0511ac94d5f3.jpg;https://image-uploader-usf.sfo2.cdn.digitaloceanspaces.com/2021/Dec/10/thiccslides/1732b49c-a097-43fe-b17c-f05bc8c80983.jpg", 
        "商品数量": 2
    }
'''
   # tables = excel_table_byname()  #可以试试这一种
   # for row in tables:
   #     print(row)

if __name__=="__main__":
    main()
